import pandas as pd
from openpyxl import load_workbook


class ExcelManager:
    def __init__(self, file_path):
        self.file_path = file_path

    def read_excel(self, sheet_name="Sheet1"):
        try:
            df = pd.read_excel(self.file_path, sheet_name=sheet_name, engine='openpyxl')
            return df
        except Exception as e:
            print(f"读取Excel文件时出错: {e}")
            return None

    def append_to_excel(self, data, sheet_name="Sheet1"):
        try:
            # 将输入数据转换为DataFrame
            df_data = pd.DataFrame(data)

            # 使用openpyxl加载现有的工作簿
            book = load_workbook(self.file_path)
            if sheet_name not in book.sheetnames:
                # 如果指定的工作表不存在，则创建一个新的DataFrame写入
                with pd.ExcelWriter(self.file_path, mode='w', engine='openpyxl') as writer:
                    writer.book = book
                    df_data.to_excel(writer, sheet_name=sheet_name, index=False)
            else:
                # 获取现有工作表的最大行数
                start_row = book[sheet_name].max_row

                with pd.ExcelWriter(self.file_path, mode='a+', engine='openpyxl', if_sheet_exists='overlay') as writer:
                    writer.book = book
                    # 写入新数据，从最后一行的下一行开始
                    df_data.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=start_row)
        except Exception as e:
            print(f"追加数据到Excel文件时出错: {e}")


# 示例：如何使用ExcelManager类
if __name__ == "__main__":
    excel_manager = ExcelManager("worker.xlsx")

    # 读取Excel文件中的数据
    data = excel_manager.read_excel()
    if data is not None:
        print("原始数据：")
        print(data.head())

    # 准备要追加的数据
    new_data = [{'Name': 'Alpha', 'Age': 29}, {'Name': 'Beta', 'Age': 34}]

    # 追加数据到Excel文件
    excel_manager.append_to_excel(new_data, sheet_name="Sheet1")

    # 再次读取以验证数据是否已成功追加
    appended_data = excel_manager.read_excel()
    if appended_data is not None:
        print("追加数据后的数据：")
        print(appended_data.tail())